﻿using System;
using System.Text;
using System.Data;
using CRMFramework;
using System.Text.RegularExpressions;

namespace CRMBiz.Group
{
    public static class GroupHandler
    {
        public static DataTable GetGroupList(int pageIndex, int pageSize)
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT * FROM crm_group where groupname='Process' or groupname='Root' or groupname='Priority Queue'";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
                if (dt.Rows.Count == 0)
                {
                    var para = new[] {
                    DBhelper.SetParameter("@ParentGroupID", "0"),
                    DBhelper.SetParameter("@GroupName", "Root"),
                    DBhelper.SetParameter("@GroupType", "Application"),
                    DBhelper.SetParameter("@Description", "Select * From view_groupdata")
                    };

                    sql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                    DBhelper.ExecuteNonQuery(sql, para);

                    sql = "SELECT GroupID FROM crm_group where groupname='Root'";
                    dt = DBhelper.ExecuteDataset(sql).Tables[0];

                    if (dt.Rows.Count > 0)
                    {
                        var para1 = new[] {
                        DBhelper.SetParameter("@ParentGroupID", dt.Rows[0]["GroupID"]),
                        DBhelper.SetParameter("@GroupName", "Process"),
                        DBhelper.SetParameter("@GroupType", "Application"),
                        DBhelper.SetParameter("@Description", "Select * From view_groupdata where app_result='1.3 Await manual process' and dupwithcontact is null and deletedFlag = 0 order by EditDate desc,app_id Desc")
                        };

                        sql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                        DBhelper.ExecuteNonQuery(sql, para1);

                        var para2 = new[] {
                        DBhelper.SetParameter("@ParentGroupID", dt.Rows[0]["GroupID"]),
                        DBhelper.SetParameter("@GroupName", "Priority Queue"),
                        DBhelper.SetParameter("@GroupType", "Application"),
                        DBhelper.SetParameter("@Description", "Select * From view_groupdata where app_result='1.3 Await manual process' and dupwithcontact is null and deletedFlag = 0 and CreatedDate  >= DATE(NOW() - INTERVAL 1 MONTH) order by PurposeSummary ASC, securities DESC, DesiredAmount DESC, CreatedDate ASC")
                        };

                        sql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                        DBhelper.ExecuteNonQuery(sql, para2);
                    }
                }
                sql = "SELECT * from crm_group order by GroupID";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static DataSet GetGroupDetail(int GroupID)
        {
            DataSet ds = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@GroupID", GroupID),
                };

                string sbSql = "SELECT * from crm_group where GroupID=@GroupID;";
                ds = DBhelper.ExecuteDataset(sbSql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static bool AddGroup(int ParentGroupID, string GroupName, string GroupType, string Description)
        {
            bool flag = false;
            try
            {
                var para1 = new[] {
                    DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                    DBhelper.SetParameter("@GroupName", GroupName),
                };

                string sbSql = "select GroupID,GroupName from crm_group where GroupID=@ParentGroupID and GroupName=@GroupName";
                DataTable dtdata = DBhelper.ExecuteDataset(sbSql, para1).Tables[0];
                if (dtdata.Rows.Count > 0)
                {
                    int ParentGroupID1 = Int32.Parse(dtdata.Rows[0]["GroupID"].ToString());
                    string GroupName1 = dtdata.Rows[0]["GroupName"].ToString();

                    if (ParentGroupID1 != ParentGroupID && GroupName1 != GroupName)
                    {
                        var para = new[] {
                            DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                            DBhelper.SetParameter("@GroupName", GroupName),
                            DBhelper.SetParameter("@GroupType", GroupType),
                            DBhelper.SetParameter("@Description", Description)
                            
                        };

                        sbSql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                        DBhelper.ExecuteNonQuery(sbSql, para);
                        flag = true;
                    }
                }
                else
                {
                    if (ParentGroupID != 0)
                    {
                        var para = new[] {
                        DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                        DBhelper.SetParameter("@GroupName", GroupName),
                        DBhelper.SetParameter("@GroupType", GroupType),
                        DBhelper.SetParameter("@Description", Description)
                        };

                        sbSql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                        DBhelper.ExecuteNonQuery(sbSql, para);
                        flag = true;
                    }
                    else
                    {
                        sbSql = "SELECT GroupID FROM crm_group where groupname='Root'";
                        dtdata = DBhelper.ExecuteDataset(sbSql).Tables[0];

                        var para = new[] {
                        DBhelper.SetParameter("@ParentGroupID", dtdata.Rows[0]["GroupID"]),
                        DBhelper.SetParameter("@GroupName", GroupName),
                        DBhelper.SetParameter("@GroupType", GroupType),
                        DBhelper.SetParameter("@Description", Description)
                        };

                        sbSql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                        DBhelper.ExecuteNonQuery(sbSql, para);
                        flag = true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool EditGroup(int GroupID, int ParentGroupID, string GroupName, string GroupType, string Description)
        {
            bool flag = false;
            try
            {
                var para1 = new[] {
                    DBhelper.SetParameter("@GroupID", GroupID)
                };

                string sbSql = "select GroupName from crm_group where GroupID=@GroupID";
                DataTable dtdata = DBhelper.ExecuteDataset(sbSql, para1).Tables[0];
                if (dtdata.Rows[0]["GroupName"].ToString() == "Process" || dtdata.Rows[0]["GroupName"].ToString() == "Root")
                {
                    flag = false;
                }
                else
                {
                    var para = new[] {
                    DBhelper.SetParameter("@GroupID", GroupID),
                    DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                    DBhelper.SetParameter("@GroupName", GroupName),
                    DBhelper.SetParameter("@GroupType", GroupType),
                    DBhelper.SetParameter("@Description", Description)
                    };

                    sbSql = "update crm_group set ParentGroupID=@ParentGroupID,GroupName=@GroupName,GroupType=@GroupType,Description=@Description where GroupID=@GroupID";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static int CheckParentGroup(int GroupID, string GroupName, int ParentGroupID)
        {
            int retvalu = 0;
            try
            {
                if (GroupName == "Process" || GroupName == "Priority Queue" || GroupName == "Root")
                    retvalu = 1;
                else
                {
                    string sql = "SELECT GroupID,ParentGroupID,(SELECT count(*) from crm_group) as Total FROM crm_group";
                    DataTable dt = DBhelper.ExecuteDataset(sql).Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < int.Parse(dt.Rows[0]["Total"].ToString()); i++)
                        {
                            if (int.Parse(dt.Rows[i]["ParentGroupID"].ToString()) == GroupID)
                                retvalu = 1;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static bool DeleteGroup(int GroupID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@GroupID", GroupID),
                };

                string sbSql = "delete from crm_group where GroupID=@GroupID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        private static string formateConditionText(string condition)
        {
            int ORcount = new Regex("OR").Matches(condition).Count;
            int Andcount = new Regex("And").Matches(condition).Count;
            if (ORcount >= 1 && Andcount == 0)
                condition = "( " + condition + " )";
            else if (ORcount > 1 && Andcount >= 1)
            {
                string[] strTemp = { };

                if (Andcount >= 1)
                    strTemp = condition.Split(new string[] { " And " }, StringSplitOptions.None);
                condition = "";
                for (int i = 0; i < strTemp.Length; i++)
                {
                    if (new Regex("OR").Matches(strTemp[i]).Count > 0)
                        strTemp[i] = formateConditionText(strTemp[i]);
                    else
                        strTemp[i] = strTemp[i];

                    if (i < strTemp.Length - 1)
                        condition += strTemp[i] + " AND  ";
                    else
                        condition += strTemp[i];
                }

            }
            return condition;
        }
        public static DataSet GetGroupDataFromQuery(string Type, string Description, string GroupName, string Days, int pageIndex, int pageSize, string sortingText)
        {
            DataSet ds = null;
            try
            {
                string[] strSeparator = new string[] { "where" };
                string[] strCondition = Description.Split(strSeparator, StringSplitOptions.None);

                string strdays = string.Empty;

                if (Days == "30")
                    strdays = " DATEDIFF(CURDATE(),DATE(EditDate)) <= 30 AND";
                else if (Days == "90")
                    strdays = "  DATEDIFF(CURDATE(),DATE(EditDate)) <= 90 AND";
                else
                    strdays = string.Empty;

                if (Type == "Direct Application" || Type == "Direct Client")
                {
                    string strColumns = string.Empty;
                    string[] query_array = Description.Split(new string[] { "from" }, StringSplitOptions.None);
                    if (Type == "Direct Client")
                    {
                        if (!string.IsNullOrEmpty(strdays))
                            strColumns = "ContactID as clientprofileid,ContactID,DateBirth,State,CreateDate as client_CreateDate, EditDate as client_EditDate, concat(FirstName,' ',MiddleName,' ',SurName) as ClientName ,(Select count(*) From " + query_array[1] + " AND " + strdays.Replace("AND", "") + ") as Total ";
                        else
                            strColumns = "ContactID as clientprofileid,ContactID,DateBirth,State,CreateDate as client_CreateDate, EditDate as client_EditDate, concat(FirstName,' ',MiddleName,' ',SurName) as ClientName ,(Select count(*) From " + query_array[1] + ") as Total ";
                    }
                    else if (Type == "Direct Application")
                    {
                        if (!string.IsNullOrEmpty(strdays))
                            strColumns = " *, udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id) AS SecondApplicant,(Select count(*) From " + query_array[1] + " AND " + strdays.Replace("AND", "") + ") as Total ";
                        else
                            strColumns = "*,udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id) AS SecondApplicant,(Select count(*) From " + query_array[1] + ") as Total ";
                    }
                    var strSql = new StringBuilder(" Select " + strColumns + " From " + query_array[1]);

                    if (!string.IsNullOrEmpty(strdays))
                        strSql.Append(" AND " + strdays.Replace("AND", ""));

                    if (!string.IsNullOrEmpty(sortingText))
                        strSql.Append(" ORDER by " + sortingText);
                    strSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(strSql.ToString());
                    return ds;
                }
                else if (Type == "Application")
                    Type = "app_id";
                else if (Type == "Client")
                    Type = "ContactID";



                if (GroupName == "Priority Queue")
                {
                    var sbSql = new StringBuilder("select * from (SELECT OfferedAmount,app_id as applicationprofileid,ContactID as clientprofileid,app_id,udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id)");
                    sbSql.Append(" AS SecondApplicant,FirstName,DesiredAmount,app_result,CreatedDate,EditDate,PurposeSummary,ReferrerVal,amoutProperties,ContactID,concat(FirstName,' ',MiddleName,' ',SurName)");
                    sbSql.Append(" as ClientName,DateBirth,State,client_EditDate,client_CreateDate,(SELECT count(*) as Total from (SELECT count(*) from view_groupdata where");

                    if (!string.IsNullOrEmpty(strdays))
                        sbSql.Append(strdays);
                    sbSql.Append(" app_result='1.3 Await manual process' and dupwithcontact is null and deletedFlag = 0 and CreatedDate  >= DATE(NOW() - INTERVAL 1 MONTH) group by app_id) as qryTotal) as Total from view_groupdata where");

                    if (!string.IsNullOrEmpty(strdays))
                        sbSql.Append(strdays);
                    sbSql.Append(" app_result='1.3 Await manual process' and dupwithcontact is null");
                    sbSql.Append(" and deletedFlag = 0 and CreatedDate  >= DATE(NOW() - INTERVAL 1 MONTH) group by app_id order by PurposeSummary ASC, securities DESC, DesiredAmount DESC, CreatedDate ASC) as temp");

                    if (!string.IsNullOrEmpty(sortingText))
                        sbSql.Append(" order by " + sortingText);
                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());
                }
                else if (GroupName == "Process")
                {
                    var sbSql = new StringBuilder("select * from (SELECT OfferedAmount,app_id as applicationprofileid,ContactID as clientprofileid,app_id,udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id)");
                    sbSql.Append(" AS SecondApplicant,FirstName,DesiredAmount,app_result,CreatedDate,EditDate,PurposeSummary,ReferrerVal,amoutProperties,ContactID,concat(FirstName,' ',MiddleName,' ',SurName)");
                    sbSql.Append(" as ClientName,DateBirth,State,client_EditDate,client_CreateDate,(SELECT count(*) as Total from (SELECT count(*) from view_groupdata where");

                    if (!string.IsNullOrEmpty(strdays))
                        sbSql.Append(strdays);
                    sbSql.Append(" app_result='1.3 Await manual process' and dupwithcontact is null and deletedFlag = 0 group by app_id) as qryTotal) as Total from view_groupdata where");

                    if (!string.IsNullOrEmpty(strdays))
                        sbSql.Append(strdays);
                    sbSql.Append(" app_result='1.3 Await manual process' and dupwithcontact is null");
                    sbSql.Append(" and deletedFlag = 0 group by app_id order by EditDate desc,app_id Desc) as temp");

                    if (!string.IsNullOrEmpty(sortingText))
                        sbSql.Append(" order by " + sortingText);
                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());
                }
                else if (strCondition.Length == 2)
                {
                    int cnt = new Regex("OR").Matches(strCondition[1]).Count;
                    if (cnt >= 1)
                        strCondition[1] = formateConditionText(strCondition[1]);
                      var sbSql = new StringBuilder("Select * from (SELECT ");

                      if (Type == "app_id")
                          sbSql.Append(" app_id,CAST(OfferedAmount as Decimal(10,0)) as OfferedAmount ,app_id as applicationprofileid,udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id) AS SecondApplicant, " +
                          " CAST(DesiredAmount as Decimal(10,0)) as DesiredAmount ,app_result,CreatedDate,EditDate,PurposeSummary,ReferrerVal,amoutProperties,(SELECT count(*) as Total from (SELECT count(*) from view_groupdata where ");
                      else if (Type == "ContactID")
                          sbSql.Append("ContactID,concat(FirstName,' ',MiddleName,' ',SurName) as ClientName,DateBirth,State,client_EditDate,client_CreateDate,ContactID as clientprofileid,(SELECT count(*) as Total from (SELECT count(*) from view_groupdata where ");

                    if (!string.IsNullOrEmpty(strdays))
                        sbSql.Append(strdays);

                    sbSql.Append(strCondition[1]);
                    sbSql.Append(" group by ").Append(Type);
                    sbSql.Append(") as qryTotal) as Total from view_groupdata where");
                    if (!string.IsNullOrEmpty(strdays))
                        sbSql.Append(strdays);
                    sbSql.Append(strCondition[1]);
                    sbSql.Append(" group by ").Append(Type + ") as temp");

                    if (!string.IsNullOrEmpty(sortingText))
                        sbSql.Append(" ORDER By " + sortingText);
                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());
                }
                else
                {
                    var sbSql = new StringBuilder("select * from (SELECT OfferedAmount,app_id as applicationprofileid,ContactID as clientprofileid,app_id,udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id) AS SecondApplicant,FirstName,DesiredAmount,app_result,CreatedDate,EditDate,PurposeSummary,ReferrerVal,amoutProperties,ContactID,concat(FirstName,' ',MiddleName,' ',SurName) as ClientName,DateBirth,State,client_EditDate,client_CreateDate,(SELECT count(*) as Total from (SELECT count(*) from view_groupdata");

                    if (!string.IsNullOrEmpty(strdays))
                        sbSql.Append(" Where " + strdays.Replace(" AND ", " "));
                    sbSql.Append(" group by ").Append(Type);
                    sbSql.Append(") as qryTotal) as Total from view_groupdata");

                    if (!string.IsNullOrEmpty(strdays))
                        sbSql.Append(" Where " + strdays.Replace(" AND ", " "));
                    sbSql.Append(" group by ").Append(Type + ") as temp");
                    
                    if (!string.IsNullOrEmpty(sortingText))
                        sbSql.Append(" order by " + sortingText);
                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataTable GetParentGroupList()
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT * FROM crm_group";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable GetFieldType(string fieldName)
        {
            DataTable dt = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@field_name", fieldName),
                };

                string sql = "SELECT DATA_TYPE FROM view_getcolumnname v where field_name=@field_name";
                dt = DBhelper.ExecuteDataset(sql, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable GetResultIDValue()
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT * FROM crm_lastresult";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable GetTreeGroupList()
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT * FROM crm_group where groupname='Process' or groupname='Root' or groupname='Priority Queue'";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
                if (dt.Rows.Count == 0)
                {
                    var para = new[] {
                    DBhelper.SetParameter("@ParentGroupID", "0"),
                    DBhelper.SetParameter("@GroupName", "Root"),
                    DBhelper.SetParameter("@GroupType", "Application"),
                    DBhelper.SetParameter("@Description", "Select * From view_groupdata")
                    };

                    sql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                    DBhelper.ExecuteNonQuery(sql, para);

                    sql = "SELECT GroupID FROM crm_group where groupname='Root'";
                    dt = DBhelper.ExecuteDataset(sql).Tables[0];

                    if (dt.Rows.Count > 0)
                    {
                        var para1 = new[] {
                        DBhelper.SetParameter("@ParentGroupID", dt.Rows[0]["GroupID"]),
                        DBhelper.SetParameter("@GroupName", "Process"),
                        DBhelper.SetParameter("@GroupType", "Application"),
                        DBhelper.SetParameter("@Description", "Select * From view_groupdata where app_result='1.3 Await manual process' and dupwithcontact is null and deletedFlag = 0 order by EditDate desc,app_id Desc")
                        };

                        sql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                        DBhelper.ExecuteNonQuery(sql, para1);

                        var para2 = new[] {
                        DBhelper.SetParameter("@ParentGroupID", dt.Rows[0]["GroupID"]),
                        DBhelper.SetParameter("@GroupName", "Priority Queue"),
                        DBhelper.SetParameter("@GroupType", "Application"),
                        DBhelper.SetParameter("@Description", "Select * From view_groupdata where app_result='1.3 Await manual process' and dupwithcontact is null and deletedFlag = 0 and CreatedDate  >= DATE(NOW() - INTERVAL 1 MONTH) order by PurposeSummary ASC, securities DESC, DesiredAmount DESC, CreatedDate ASC")
                        };

                        sql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description) values(@ParentGroupID,@GroupName,@GroupType,@Description)";
                        DBhelper.ExecuteNonQuery(sql, para2);
                    }
                }
                sql = "SELECT * FROM crm_group";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }
    }
}
